import os

os.chdir(r'D:\A_Document\A_Microsoft_VS_Code\Python')

import pandas as pd

# 读取user文件
df_user = pd.read_csv('users.csv')

# 将df_user的4个列赋给df
df = df_user[['user_id', 'name', 'age', 'gender']]

# 读取behavior文件, behavior文件中只包含user_id、item_id和action三列, action包括buy、view、rate
df_behavior = pd.read_csv('user_behavior.csv')

# 读取items文件
df_item = pd.read_csv('items.csv')

# 统计每个用户购买商品的次数
df1 = df_behavior[df_behavior['action'] == 'buy'].groupby('user_id').size().reset_index()
# 将列名改为buy_count
df1.columns = ['user_id', 'buy_count']
# 将df1的buy_count列赋给df
df['buy_count'] = df1['buy_count']

# 统计每个用户浏览商品的次数
df1 = df_behavior[df_behavior['action'] == 'view'].groupby('user_id').size().reset_index()
# 将列名改为view_count
df1.columns = ['user_id', 'view_count']
# 将df1的view_count列与df的buy_count列相加, 将列加给df
df['view_count'] = df1['view_count'] + df['buy_count']

# 统计每个用户评分商品的次数
df1 = df_behavior[df_behavior['action'] == 'rate'].groupby('user_id').size().reset_index()
# 将列名改为rate_count
df1.columns = ['user_id', 'rate_count']
# 将df1的rate_count列加给df
df['rate_count'] = df1['rate_count']

# 统计每个用户评分高于2.5的次数
df1 = df_behavior[(df_behavior['action'] == 'rate') & (df_behavior['rating'] > 2.5)].groupby('user_id').size().reset_index()
# 将列名改为good_rating    
df1.columns = ['user_id', 'good_rating']
# 将df1的good_rating列加给df
df['good_rating'] = df1['good_rating']

# 统计每个用户的总消费, df_behavior中只有item_id，需要借助df_item
df1 = df_behavior.merge(df_item[['item_id', 'price']], on='item_id', how='left') # 将df_behavior和df_item合并
df1 = df1[df1['action'] == 'buy'].groupby('user_id').sum().reset_index() # 统计每个用户的总消费
df['total_cost'] = df1['price'] # 将列加给df

# 统计每个用户的平均消费, 但是cost是str类型, 需要先转换成float类型, 保留两位小数
df['total_cost'] = df['total_cost'].astype(float).round(2) # 将列total_cost转换成float类型, 保留两位小数
df['average_cost'] = df['total_cost'] / df['buy_count'] # 计算平均消费

# 统计每个用户的评分合计
df1 = df_behavior[df_behavior['action'] == 'rate'].groupby('user_id').sum().reset_index() # 统计每个用户的评分合计
df['total_rating'] = df1['rating'] # 将列加给df

# 统计每个用户的平均评分
df['average_rating'] = df['total_rating'] / df['rate_count'] # 计算平均评分

# 保存df
df.to_csv('user_data.csv', index=False)